create table Persona (Codigo nvarchar(15) primary key not null
						,Nombre nvarchar(50) not null
						,Apellido nvarchar(50) not null
						,Telefono nvarchar(25)
						,Direccion nvarchar(60)not null
						,CI nvarchar(7) not null)

------------------------------------------------------
declare @nombre nvarchar(50),@apellido nvarchar(50),@telf nvarchar(25),@dir nvarchar(60),@ci nvarchar(15),@type nvarchar(2),@Bid nvarchar(5)
declare @tipo nvarchar(2),@ap nvarchar(1),@nom nvarchar(1),@cod nvarchar(9),@largo int,@ceros nvarchar(9),@ceros2 nvarchar(9),@Codigo nvarchar(15),@Carnet nvarchar(7) 
set @ceros='000000000'
declare qcopiar cursor for 
							select FirstName,LastName,PhoneNumber,AddressLine1,NationalIDNumber,PersonType,p.BusinessEntityID
									from AdventureWorks2012.Person.Person as p
										join AdventureWorks2012.Person.PersonPhone as pph on p.BusinessEntityID=pph.BusinessEntityID
										join AdventureWorks2012.Person.BusinessEntityAddress as bea on p.BusinessEntityID=bea.BusinessEntityID
										join AdventureWorks2012.Person.Address as pa on bea.AddressID=pa.AddressID
										join AdventureWorks2012.HumanResources.Employee as he on p.BusinessEntityID=he.BusinessEntityID
open qcopiar 
fetch next from qcopiar INTO @nombre,@apellido,@telf,@dir,@ci,@type,@Bid
while @@FETCH_STATUS=0
begin
set @tipo=@type
set @ap=LEFT(@apellido,1)
set @nom=LEFT(@nombre,1)
set @largo=len(@Bid)
set @ceros2=LEFT(9-@largo,@ceros)
set @cod=CONCAT(@ceros2,@Bid)
set @Codigo=concat(@tipo,'-',@ap,@nom,'-',@cod)
set @Carnet=LEFT(@ci,7)
insert into Persona values (@Codigo,@nombre,@apellido,@telf,@dir,@Carnet)
fetch next from qcopiar INTO @nombre,@apellido,@telf,@dir,@ci,@type,@Bid
end
close qcopiar
deallocate qcopiar
--------------------------------------------------------------------------
create table Asistencia (Id int primary key Identity(1,1)
						,CI nvarchar (7) not null
						,Fecha date not null default CURRENT_TIMESTAMP
						,Ingreso int not null
						,Fecha_Regular nvarchar(max) )
----------------------------------------------------------------------
create procedure marcador
@Ci nvarchar (7)
as
declare @horaingreso nvarchar(max),@res nvarchar(max)
set @horaingreso=DATEPART(HOUR,GETDATE())
if (@horaingreso=8)
begin
insert into Asistencia values (@Ci,CURRENT_TIMESTAMP,0,'8:00')
set @res='entrando'
print @res
end
else
if (@horaingreso=14)
begin
insert into Asistencia values (@Ci,CURRENT_TIMESTAMP,0,'14:00')
set @res='entrando'
print @res
end
else
if (@horaingreso=12)
begin
insert into Asistencia values (@Ci,CURRENT_TIMESTAMP,1,'12:00')
set @res='saliendo'
print @res
end
else
if (@horaingreso=14)
begin
insert into Asistencia values (@Ci,CURRENT_TIMESTAMP,1,'14:00')
set @res='saliendo'
print @res
end
--------------------------------------
create procedure informe
@Ci
as
select Nombre+' '+Apellido as Nombre,Fecha,Ingreso
from Asistencia as a
join Persona as p on p.CI=a.CI
where @Ci=p.CI
